package cn.ittest.dao.impl;
/*
* 持久层实现类
* */

import cn.ittest.dao.StudentDao;
import cn.ittest.domain.Student;
import cn.ittest.util.JDBCUtil;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class StudentDaoImpl implements StudentDao {
    private Connection con = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    private int count = 0;
    //查询所有操作
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        String sql = "select * from student";
        try {
            con = JDBCUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                int sid = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                Date birthday = rs.getDate(4);
                Student stu = new Student(sid,name,age,birthday);
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con,ps,rs);
        }

        return list;
    }
    //有条件查询
    @Override
    public Student findById(Integer id) {
        Student stu = null;
        try {
            con = JDBCUtil.getConnection();
            String sql = "select * from student where sid = ? ";
            ps = con.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                stu = new Student(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getDate(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stu;
    }
    //新增数据
    @Override
    public int insert(Student stu) {
        try {
            con = JDBCUtil.getConnection();
            String sql = "insert into student values (null,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1,stu.getName());
            ps.setInt(2,stu.getAge());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            ps.setDate(3, Date.valueOf(sdf.format(stu.getBirthday())));
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con,ps);
        }


        return count;
    }
    //删除数据
    @Override
    public int delete(Student stu) {
        try {
            con = JDBCUtil.getConnection();
            String sql = "delete from student where sid = ? ";
            ps = con.prepareStatement(sql);
            ps.setInt(1,stu.getSid());
           count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    //修改数据
    @Override
    public int update(Student stu) {
        try {
            con = JDBCUtil.getConnection();
            String sql = "update student set name = ?,age = ? where sid = ?" ;
            ps = con.prepareStatement(sql);
            ps.setString(1,stu.getName());
            ps.setInt(2,stu.getAge());
            ps.setInt(3,stu.getSid());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con,ps);
        }
        return count;
    }
}
